package com.kehutong.user.dao;

import org.coraframework.orm.annotation.Dao;
import org.coraframework.orm.annotation.Mapper;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.execute.Condition;

import java.util.HashMap;
import java.util.Map;

@Dao
public interface AssetsUserDao {

    @Mapper(
            genericType = HashMap.class,
            countSQL = "select COUNT(1) FROM  " +
                    " (SELECT  " +
                    "  DISTINCT  " +
                    " a.id,  " +
                    " a.realname,  " +
                    " a.nickname," +
                    " a.avatar," +
                    " a.firstLetter," +
                    " a.mobile,  " +
                    " a.createTime  " +
                    " FROM  " +
                    " t_resident a,  " +
                    " t_resident_house b  " +
                    " WHERE  " +
                    " 1 = 1  " +
                    " AND a.id = b.residentNo  " +
                    " AND b.bindType IN('0','1')  " +
                    " AND a.deleted = FALSE  " +
                    " AND ${}) t",
            sql = "SELECT" +
                    "  DISTINCT" +
                    "  a.id," +
                    "  a.realname," +
                    "  a.nickname," +
                    "  a.avatar," +
                    "  a.firstLetter," +
                    "  a.mobile," +
                    "  a.createTime" +
                    " FROM" +
                    " t_resident a," +
                    " t_resident_house b" +
                    " WHERE" +
                    " 1 = 1" +
                    " AND a.id = b.residentNo" +
                    " AND b.bindType IN('0','1')" +
                    " AND a.deleted = FALSE" +
                    " AND ${}"
    )
    public Page<Map<String, Object>> loadPage(Condition... condition);


    @Mapper(
            genericType = HashMap.class,
            countSQL = "SELECT COUNT(1) FROM(  " +
                    "SELECT   " +
                    "  communityNo,  " +
                    "  communityName,  " +
                    "  residentNo,  " +
                    "  realname,  " +
                    "  sum(if(houseType = 1, 1, 0)) as houseCount,  " +
                    "  sum(if(houseType = 2, 1, 0)) as shopCount,  " +
                    "  sum(if(houseType = 3, 1, 0)) as parkingCount  " +
                    " FROM t_resident_house   " +
                    " WHERE" +
                    " 1 = 1" +
                    " AND ${} "+
                    ") t",
            sql = "SELECT   " +
                    "  communityNo," +
                    "  communityName," +
                    "  residentNo," +
                    "  realname," +
                    "  sum(if(houseType = 1, 1, 0)) as houseCount," +
                    "  sum(if(houseType = 2, 1, 0)) as shopCount," +
                    "  sum(if(houseType = 3, 1, 0)) as parkingCount" +
                    " FROM t_resident_house" +
                    " WHERE" +
                    " 1 = 1" +
                    " AND ${} "
    )
    public Page<Map<String, Object>> loadUserCommunityPage(Condition... condition);


    @Mapper(
            genericType = HashMap.class,
            sql="SELECT COUNT(DISTINCT communityNo) as count,group_concat(DISTINCT communityName) AS `name` FROM t_resident_house WHERE ${}"
    )
    Map<String, Object> getUserCommunityCount(Condition... condition);
}
